﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Data;

namespace Client_Revenue
{
    public partial class Revenue : System.Web.UI.Page
    {

        #region "Properties & Fields"

        DateTime receivedDate { get; set; }
        int startRow { get; set; }
        
        private string CName { get; set; }

        private DateTime ReceivedDate
        {
            get
            {
                if (receivedDate == null)
                    return DateTime.Now;

                return (DateTime)this.receivedDate;
            }
            set { receivedDate = value; }
        }
        #endregion


        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                startRow = 0;//Convert.ToInt32(Request.QueryString["startRow"]);
                ShowAddedCommissions(ReceivedDate);

            }

        }

        protected void bnUpload_Click(object sender, EventArgs e)
        {
            if (CarrierComFileUpload.HasFile)
            {
                if (CarrierComFileUpload.PostedFile.FileName == "")
                {
                    lblInfo.Text = "No file specified.";
                }
                else
                {
                    try
                    {
                        string CarrierCommFileName = Path.GetFileName(CarrierComFileUpload.PostedFile.FileName);
                        CarrierComFileUpload.PostedFile.SaveAs(MapPath("/") + CarrierCommFileName);

                        lblInfo.Text = "File " + CarrierCommFileName;
                        lblInfo.Text += " uploaded successfully.";

                        //Gets commissions from Check Received worksheet
                        GetCommission(MapPath("/") + (CarrierCommFileName));

                        //Get Added Commissions for display

                        //pnUpload.Visible = false;
                    }
                    catch (Exception err)
                    {
                        lblInfo.Text = err.Message;

                    }
                }
            }
        }

        private void GetCommission(string filePath)
        {
            try
            {

                using (var DE = new dominiumEntities())
                {

                    CarrierCommission commissions = new CarrierCommission();

                    var fileName = filePath;
                    string connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties=""Excel 8.0;HDR=YES; READONLY=FALSE;""", fileName);

                    OleDbConnection conn = new OleDbConnection(connectionString);

                    conn.Open();
                    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [Checks Received1$]", conn);
                    DataSet dsCarrierComissions = new DataSet();

                    da.Fill(dsCarrierComissions);

                    System.Data.DataTable dt = dsCarrierComissions.Tables[0];
                    foreach (DataRow row in dsCarrierComissions.Tables[0].Rows)
                    {

                        string rcvdDate = row["Date"].ToString().Trim();
                        string invNum = row["Num"].ToString().Trim();
                        string rcvdAmount = row["Amount"].ToString().Trim();
                        string carrierName = row["Name"].ToString().Trim();
                        if (carrierName != null | carrierName == "")
                        {
                            CName = carrierName;
                            commissions.CarrierId = DE.Carriers.Where(c => c.CarrierName.ToLower().Contains(carrierName.ToLower())).Select(c => c.CarrierId).FirstOrDefault();
                        }
                        commissions.Commission_ReceivedAmt = Decimal.Parse(rcvdAmount);
                        commissions.ReceiptDate = DateTime.Parse(rcvdDate);
                        commissions.QB_InvoiceNum = invNum;
                        commissions.RevenueTypeId = (DE.RevenueTypes.Where(r => r.RevenueTypeName == "Commission").Select(r => r.RevenueTypeId).FirstOrDefault());
                        commissions.Commission = 0;

                        //Add commissions to database
                        DE.AddCarrierCommissions(commissions.CarrierId, commissions.RevenueTypeId, commissions.Commission_ReceivedAmt, commissions.Commission, commissions.ReceiptDate, commissions.QB_InvoiceNum);

                    }

                    ShowAddedCommissions(commissions.ReceiptDate);
                    this.receivedDate = commissions.ReceiptDate;

                    da.Dispose();
                    conn.Dispose();
                }

            }
            catch (Exception ex)
            {
                lblInfo.Text = "Unable to find " + CName + "! <br/>Make sure Name spelt right or Carrier is in database <br/>" + ex.InnerException.Message;

            }
        }

        private void ShowAddedCommissions(DateTime mReceivedDated)
        {
            using (var DE = new dominiumEntities())
            {
                var commissionsAdded = (from cc in DE.CarrierCommissions
                                        join c in DE.Carriers on cc.CarrierId equals c.CarrierId
                                        join r in DE.RevenueTypes on cc.RevenueTypeId equals r.RevenueTypeId
                                        where cc.ReceiptDate.Month == mReceivedDated.Month
                                        select new
                                            {
                                                cc.CarrierId,
                                                c.CarrierName,
                                                r.RevenueTypeName,
                                                r.RevenueTypeId,
                                                cc.Commission_ReceivedAmt,
                                                cc.Commission,
                                                cc.QB_InvoiceNum,
                                                cc.ReceiptDate
                                            }).ToList();

                gvCommissions.DataSource = commissionsAdded;//.Skip(startRow).Take(10)
                gvCommissions.DataBind();
            }
        }

        protected void gvCommissions_SelectedIndexChanged(object sender, EventArgs e)
        {


        }

        protected void gvCommissions_PageIndexChanging(object sender, GridViewPageEventArgs e)
        { 
            //if(e.NewPageIndex ==2)
            //    startRow = 51;
            //Response.Redirect("~/Revenue.aspx?startRow=" + startRow);
           
            
        }

        protected void btnReceivedComm_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(txtReceivedDate.Text))
            {
                receivedDate = DateTime.Parse(txtReceivedDate.Text); 
                ShowAddedCommissions(ReceivedDate);
            }
           
        }

    }
}